package com.icss.ah.base.util;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.List;

import javax.net.ssl.HostnameVerifier;
import javax.net.ssl.HttpsURLConnection;
import javax.net.ssl.SSLContext;
import javax.net.ssl.SSLSession;
import javax.net.ssl.SSLSocketFactory;
import javax.net.ssl.TrustManager;
import javax.net.ssl.X509TrustManager;
import javax.security.cert.CertificateException;
import javax.security.cert.X509Certificate;

import jxl.SheetSettings;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import com.icss.ah.orderList.bean.OrderBean;
import com.icss.ah.orderList.bean.ProductBean;

public class ExcelUtil {
	
	public static void createStockExcel(OutputStream os,List<OrderBean> list,String fname) throws WriteException,IOException{
    	WritableFont wfTitle= new WritableFont(WritableFont.ARIAL,16,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
    	WritableCellFormat wcfTitle= new WritableCellFormat(wfTitle);	
    	
    	WritableCellFormat formateTitle=new WritableCellFormat(wcfTitle);
    	formateTitle.setAlignment(jxl.format.Alignment.CENTRE);
    	formateTitle.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
    	formateTitle.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
    	formateTitle.setWrap(true);
    	
    	WritableCellFormat formateTitle1=new WritableCellFormat(wcfTitle);
    	formateTitle1.setAlignment(jxl.format.Alignment.CENTRE);
    	formateTitle1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
    	formateTitle1.setBorder(jxl.format.Border.LEFT,jxl.format.BorderLineStyle.THIN);
    	formateTitle1.setBorder(jxl.format.Border.TOP,jxl.format.BorderLineStyle.THIN);
    	formateTitle1.setBorder(jxl.format.Border.BOTTOM,jxl.format.BorderLineStyle.THIN);
    	formateTitle1.setWrap(true);
    	
    	WritableFont wfc= new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
    	WritableCellFormat wcfFC= new WritableCellFormat(wfc);
    	//属性
    	WritableCellFormat formateHead=new WritableCellFormat(wcfFC);
    	formateHead.setAlignment(jxl.format.Alignment.CENTRE);
    	formateHead.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
    	formateHead.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
    	formateHead.setBackground(Colour.AQUA);
    	formateHead.setWrap(true);
    	
    	WritableCellFormat formateBody = new  WritableCellFormat(); 
    	formateBody.setAlignment(jxl.format.Alignment.CENTRE);
    	formateBody.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
    	formateBody.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
    	formateBody.setWrap(true);
    	
    	WritableCellFormat formateBody1 = new  WritableCellFormat(); 
    	formateBody1.setAlignment(jxl.format.Alignment.LEFT);
    	formateBody1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
    	formateBody1.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
    	formateBody1.setWrap(true);
    	
    	WritableCellFormat formateBody2 = new  WritableCellFormat(); 
    	formateBody2.setAlignment(jxl.format.Alignment.LEFT);
    	formateBody2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
    	formateBody2.setBorder(jxl.format.Border.RIGHT,jxl.format.BorderLineStyle.THIN);
    	formateBody2.setBorder(jxl.format.Border.TOP,jxl.format.BorderLineStyle.THIN);
    	formateBody2.setBorder(jxl.format.Border.BOTTOM,jxl.format.BorderLineStyle.THIN);
    	formateBody2.setWrap(true);
    	//创建工作薄
    	WritableWorkbook workbook = Workbook.createWorkbook(os);
    	//创建新的一页
    	WritableSheet sheet = workbook.createSheet(fname,0);
    	SheetSettings setting = sheet.getSettings(); 
    	setting.setTopMargin(0.00);    // 上  
        setting.setBottomMargin(0.00); // 下
    	setting.setLeftMargin(0.21);   // 左  
        setting.setRightMargin(0.20);  // 右  
    	
    	sheet.setColumnView(0,5);
    	sheet.setColumnView(1,25);
    	sheet.setColumnView(2,12);
    	sheet.setColumnView(3,15);
    	sheet.setColumnView(4,15);
    	sheet.setColumnView(5,8);
    	sheet.setColumnView(6,8);
    	sheet.setColumnView(7,10);
    	
    	int xh = 0;
    	if(list!=null && list.size()>0){
    		for(OrderBean infoBean:list){
    			sheet.setRowView(xh,1000);
    			sheet.mergeCells( 0, xh, 7, xh++);
    			sheet.setRowView(xh,800);
    			sheet.mergeCells( 0, xh, 1, xh); 
    			sheet.addCell(new Label(0,xh,"徽映优选",formateTitle1));
    			sheet.mergeCells(2, xh, 7, xh); 
    			String orderInfo = "下单时间："+infoBean.getCREATE_TIME()+"  收货人："+infoBean.getUSER_NAME()+"  收货电话："+
    				infoBean.getCON_TEL()+"  收货地址："+infoBean.getPROVINCE()+infoBean.getCITY()+infoBean.getCOUNTY()+
    				infoBean.getCON_ADDRESS()+"  备注："+infoBean.getORDER_STATUS_NODE();
    			sheet.addCell(new Label(2,xh++,orderInfo,formateBody2));
    	    	
    			List<ProductBean> productList = infoBean.getProducts();
    			int proCount = productList.size();
    			int col = 0;
    			sheet.setRowView(xh,400);
    			sheet.addCell(new Label(col++,xh,"序号",formateBody));
    			sheet.addCell(new Label(col++,xh,"商品名称",formateBody));
    			sheet.addCell(new Label(col++,xh,"规格",formateBody));
    			sheet.addCell(new Label(col++,xh,"条码",formateBody));
    			sheet.addCell(new Label(col++,xh,"优惠信息",formateBody));
    			sheet.addCell(new Label(col++,xh,"单价",formateBody));
    			sheet.addCell(new Label(col++,xh,"数量",formateBody));
    			sheet.addCell(new Label(col++,xh++,"小计",formateBody));
    			
    			String yhxx = infoBean.getYHXX();
    			String yhStr = "";
    			if(yhxx!=null&&!"".equals(yhxx)){
    				if(yhxx.indexOf(";")>0){
    					String[] yhArr = yhxx.split(";");
    					for(int i=0;i<yhArr.length;i++){
    						yhStr += (i+1)+"、"+yhArr[i]+";  ";
    					}
    				}else{
    					yhStr += "1、"+yhxx+";  ";
    				}
    			}
    			sheet.mergeCells( 4, xh, 4, (xh+proCount-1)); 
				sheet.addCell(new Label(4,xh,yhStr,formateBody));
    			int index = 1;
    			int proNum = 0;
    			BigDecimal proPrice = BigDecimal.ZERO;
    			for(ProductBean product:productList){
    				col = 0;
    				String proName = product.getPRODUCT_NAME();
    				int num = proName.length()%14==0 ? proName.length()/14 : proName.length()/14+1;
    				sheet.setRowView(xh,400*num);
    				sheet.addCell(new Label(col++,xh,index+++"",formateBody));
    				sheet.addCell(new Label(col++,xh,product.getPRODUCT_NAME(),formateBody));
    				sheet.addCell(new Label(col++,xh,product.getPARAM_VALUE(),formateBody));
    				sheet.addCell(new Label(col++,xh,product.getBAR_CODE(),formateBody));
    				sheet.addCell(new Label(++col,xh,product.getPRODUCT_PRICE(),formateBody));
    				sheet.addCell(new Label(++col,xh,product.getSELL_AMOUNT(),formateBody));
    				BigDecimal subtotal = new BigDecimal(product.getPRODUCT_PRICE()).multiply(new BigDecimal(product.getSELL_AMOUNT()));
    				sheet.addCell(new Label(++col,xh++,subtotal.toString(),formateBody));
    				proNum += Integer.valueOf(product.getSELL_AMOUNT());
    				proPrice = proPrice.add(subtotal);
    			}
    			sheet.setRowView(xh,400);
    			sheet.mergeCells( 0, xh, 3, xh); 
    			BigDecimal discounts = new BigDecimal(infoBean.getSUM_SELL_MONEY()).subtract(new BigDecimal(infoBean.getSUM_REAL_MONEY()));
    			String info = "货款："+infoBean.getSUM_SELL_MONEY()+"     优惠："+discounts.toString()+"     应付款："+infoBean.getSUM_REAL_MONEY()+"     已付款："+infoBean.getPAY_MONEY();
    			sheet.addCell(new Label(0,xh,info,formateBody1));
    			sheet.mergeCells( 4, xh, 5, xh); 
    			String payOnline = infoBean.getPAY_ONLINE();
    			payOnline = payOnline != null && payOnline.equals("1") ? "在线支付" : "货到付款";
    			sheet.addCell(new Label(4,xh,payOnline,formateTitle1));
    			sheet.addCell(new Label(6,xh,proNum+"",formateBody));
    			sheet.addCell(new Label(7,xh++,proPrice.toString(),formateBody));
    			
    			sheet.setRowView(xh,400);
    			String supInfo = "供货商："+infoBean.getSHOP_NAME()+"   供货商电话："+infoBean.getSHOP_TEL()+"   地址："+infoBean.getSHOP_ADDRESS();
    			sheet.mergeCells( 0, xh, 7, xh); 
    			sheet.addCell(new Label(0,xh++,supInfo,formateBody1));
    			
    			sheet.setRowView(xh,400);
    			String sendInfo = "送货员："+"          "+"   送货时间："+"          "+"   超市签字（我确认已收到相应货品）：";
    			sheet.mergeCells( 0, xh, 7, xh); 
    			sheet.addCell(new Label(0,xh++,sendInfo,formateBody1));
    			
    			sheet.setRowView(xh,400);
    			String hyInfo = "超市进货   徽映优选（www.jiefangyx.com）"+"              订单打印时间："+DateUtils.getCurrentDate("yyyy/MM/dd HH:mm:ss");
    			sheet.mergeCells( 0, xh, 7, xh); 
    			sheet.addCell(new Label(0,xh++,hyInfo,formateBody1));
    		}
    	}
    	
    	//把创建的内容写入到输出流中，并关闭输出流
    	workbook.write();
    	workbook.close();
    	os.close();
}

	public static void createStockExcel2(OutputStream os,List<OrderBean> list,String fname) throws WriteException,IOException{
	    	WritableFont wfTitle= new WritableFont(WritableFont.ARIAL,16,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
	    	WritableCellFormat wcfTitle= new WritableCellFormat(wfTitle);	
	    	
	    	WritableCellFormat formateTitle=new WritableCellFormat(wcfTitle);
	    	formateTitle.setAlignment(jxl.format.Alignment.CENTRE);
	    	formateTitle.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
	    	formateTitle.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
	    	formateTitle.setWrap(true);
	    	
	    	WritableFont wfc= new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
	    	WritableCellFormat wcfFC= new WritableCellFormat(wfc);
	    	//属性
	    	WritableCellFormat formateHead=new WritableCellFormat(wcfFC);
	    	formateHead.setAlignment(jxl.format.Alignment.CENTRE);
	    	formateHead.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
	    	formateHead.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
	    	formateHead.setBackground(Colour.AQUA);
	    	formateHead.setWrap(true);
	    	
	    	WritableCellFormat formateBody = new  WritableCellFormat(); 
	    	formateBody.setAlignment(jxl.format.Alignment.LEFT);
	    	formateBody.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
	    	formateBody.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
	    	formateBody.setWrap(true);
	    	//创建工作薄
	    	WritableWorkbook workbook = Workbook.createWorkbook(os);
	    	//创建新的一页
	    	WritableSheet sheet = workbook.createSheet(fname,0);
	    	
	    	sheet.setRowView(0,460);
	    	sheet.setRowView(1,400);
	    	
	    	sheet.setColumnView(0,10);
	    	sheet.setColumnView(1,13);
	    	sheet.setColumnView(2,12);
	    	sheet.setColumnView(3,13);
	    	sheet.setColumnView(4,10);
	    	sheet.setColumnView(5,20);
	    	sheet.setColumnView(6,10);
	    	int columnViewIndex = 6;	//记录设置列宽度的索引
	    	
	    	//创建要显示的内容,创建一个单元格，第一个参数为列坐标，第二个参数为行坐标，第三个参数为内容
	    	sheet.addCell(new Label(0,0,fname,formateTitle));
	    	
	    	sheet.mergeCells(0,0,columnViewIndex,0);
	    	int xh = 1;
	    	int col = 0;
	    	sheet.addCell(new Label(col++,xh,"成交时间",formateHead));
	    	sheet.addCell(new Label(col++,xh,"买家",formateHead));
	    	sheet.addCell(new Label(col++,xh,"买家电话",formateHead));
	    	sheet.addCell(new Label(col++,xh,"买家地址",formateHead));
	    	sheet.addCell(new Label(col++,xh,"实收款",formateHead));
	    	sheet.addCell(new Label(col++,xh,"商品名称",formateHead));
	    	sheet.addCell(new Label(col++,xh++, "数量", formateHead));
	    	
	    	if(list!=null && list.size()>0){
	    		for(OrderBean infoBean:list){   
	    			int columnIndex = 5;//记录设置列内容的索引
	    			List<ProductBean> productList = infoBean.getProducts();
	    			int productCount = productList.size();
	    			col = 0;
	    			sheet.mergeCells( col, xh, col++, xh+productCount); 
	    			sheet.mergeCells( col, xh, col++, xh+productCount); 
	    			sheet.mergeCells( col, xh, col++, xh+productCount); 
	    			sheet.mergeCells( col, xh, col++, xh+productCount); 
	    			sheet.mergeCells( col, xh, col++, xh+productCount); 
	    			col = 0;
	    			String orderTime = infoBean.getCREATE_TIME();
	    			sheet.addCell(new Label(col++,xh,orderTime,formateBody));
	    			sheet.addCell(new Label(col++,xh,infoBean.getUSER_NAME(),formateBody));
	    			sheet.addCell(new Label(col++,xh,infoBean.getCON_TEL(),formateBody));
	    			String addr = infoBean.getPROVINCE()+infoBean.getCITY()+infoBean.getCOUNTY()+infoBean.getCON_ADDRESS();
	    			sheet.addCell(new Label(col++,xh,addr,formateBody));
	    			sheet.addCell(new Label(col++,xh,infoBean.getSUM_REAL_MONEY(),formateBody));
	    			for(ProductBean product:productList){
	    				int columnIndex_d = columnIndex;
	    				sheet.addCell(new Label(columnIndex_d++,xh,product.getPRODUCT_NAME()+"",formateBody));
	    				sheet.addCell(new Label(columnIndex_d++,xh++,product.getPRODUCT_PRICE()+"*"+product.getSELL_AMOUNT(),formateBody));
	    			}
	    			String yhxx = infoBean.getYHXX();
	    			String yhStr = "";
	    			if(yhxx!=null&&!"".equals(yhxx)){
	    				if(yhxx.indexOf(";")>0){
	    					String[] yhArr = yhxx.split(";");
	    					for(int i=0;i<yhArr.length;i++){
	    						yhStr += (i+1)+"、"+yhArr[i]+";  ";
	    					}
	    				}else{
	    					yhStr += "1、"+yhxx+";  ";
	    				}
	    			}
	    			sheet.mergeCells(columnIndex, xh, columnIndex+1, xh); 
	    			sheet.addCell(new Label(columnIndex,xh,"优惠信息："+yhStr,formateBody));
	    			xh++;
	    		}
	    	}
	    	
	    	//把创建的内容写入到输出流中，并关闭输出流
	    	workbook.write();
	    	workbook.close();
	    	os.close();
    }
	public static void createStockExcel1(OutputStream os,List<OrderBean> list,String fname) throws WriteException,IOException{
		WritableFont wfTitle= new WritableFont(WritableFont.ARIAL,16,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
		WritableCellFormat wcfTitle= new WritableCellFormat(wfTitle);	
		
		WritableCellFormat formateTitle=new WritableCellFormat(wcfTitle);
		formateTitle.setAlignment(jxl.format.Alignment.CENTRE);
		formateTitle.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
		formateTitle.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
		formateTitle.setWrap(true);
		
		WritableFont wfc= new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
		WritableCellFormat wcfFC= new WritableCellFormat(wfc);
		//属性
		WritableCellFormat formateHead=new WritableCellFormat(wcfFC);
		formateHead.setAlignment(jxl.format.Alignment.CENTRE);
		formateHead.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
		formateHead.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
		formateHead.setBackground(Colour.AQUA);
		formateHead.setWrap(true);
		
		WritableCellFormat formateBody = new  WritableCellFormat(); 
		formateBody.setAlignment(jxl.format.Alignment.LEFT);
		formateBody.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
		formateBody.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
		formateBody.setWrap(true);
		//创建工作薄
		WritableWorkbook workbook = Workbook.createWorkbook(os);
		//创建新的一页
		WritableSheet sheet = workbook.createSheet(fname,0);
		
		sheet.setRowView(0,460);
		sheet.setRowView(1,400);
		
		sheet.setColumnView(0,10);
		sheet.setColumnView(1,20);
		sheet.setColumnView(2,12);
		sheet.setColumnView(3,20);
		sheet.setColumnView(4,10);
		sheet.setColumnView(5,20);
		sheet.setColumnView(6,10);
//	    	sheet.setColumnView(7,18);
//	    	sheet.setColumnView(8,18);
//	    	sheet.setColumnView(9,20);
//	    	sheet.setColumnView(10,18);
//	    	sheet.setColumnView(11,18);
//	    	sheet.setColumnView(12,20);
		int columnViewIndex = 6;	//记录设置列宽度的索引
		
		//创建要显示的内容,创建一个单元格，第一个参数为列坐标，第二个参数为行坐标，第三个参数为内容
		sheet.addCell(new Label(0,0,fname,formateTitle));
		
		sheet.mergeCells(0,0,columnViewIndex,0);
		int xh = 1;
		int col = 0;
//	    	sheet.addCell(new Label(col++,xh,"序号",formateHead));
//	    	sheet.addCell(new Label(col++,xh,"订单号",formateHead));
		sheet.addCell(new Label(col++,xh,"成交时间",formateHead));
		sheet.addCell(new Label(col++,xh,"买家",formateHead));
		sheet.addCell(new Label(col++,xh,"买家电话",formateHead));
		sheet.addCell(new Label(col++,xh,"买家地址",formateHead));
//	    	sheet.addCell(new Label(col++,xh,"交易状态",formateHead));
		sheet.addCell(new Label(col++,xh,"实收款",formateHead));
//	    	sheet.addCell(new Label(col++,xh,"优惠信息",formateHead));
		sheet.addCell(new Label(col++,xh,"商品名称",formateHead));
		sheet.addCell(new Label(col++,xh++, "数量", formateHead));
//	    	sheet.addCell(new Label(col++,xh++, "商品图片", formateHead));
//	    	int formateIndex = 9;	//记录设置列标题的索引
		
		if(list!=null && list.size()>0){
			int count = 1;
			//String taskDate ="";
			//String taskWeek ="";
			for(OrderBean infoBean:list){   
				int columnIndex = 5;//记录设置列内容的索引
				List<ProductBean> productList = infoBean.getProducts();
				int productCount = productList.size();
//	    			productCount = productCount*5;//-1;
				col = 0;
				sheet.mergeCells( col, xh, col++, xh+productCount); 
				sheet.mergeCells( col, xh, col++, xh+productCount); 
				sheet.mergeCells( col, xh, col++, xh+productCount); 
				sheet.mergeCells( col, xh, col++, xh+productCount); 
				sheet.mergeCells( col, xh, col++, xh+productCount); 
//	    			sheet.mergeCells( col, xh, col++, xh+productCount); 
//	    			sheet.mergeCells( col, xh, col++, xh+productCount); 
//	    			sheet.mergeCells( col, xh, col++, xh+productCount); 
//	    			sheet.mergeCells( col, xh, col++, xh+productCount); 
				
				col = 0;
//	    			sheet.addCell(new Label(col++,xh,count+++"",formateBody));
//	    			sheet.addCell(new Label(col++,xh,infoBean.getORDER_NO(),formateBody));
				String orderTime = infoBean.getCREATE_TIME();
//	    			if(orderTime.length()>19){
//	    				orderTime = orderTime.substring(0, 19);
//	    			}
				sheet.addCell(new Label(col++,xh,orderTime,formateBody));
				sheet.addCell(new Label(col++,xh,infoBean.getUSER_NAME(),formateBody));
				sheet.addCell(new Label(col++,xh,infoBean.getCON_TEL(),formateBody));
				String addr = infoBean.getPROVINCE()+infoBean.getCITY()+infoBean.getCOUNTY()+infoBean.getCON_ADDRESS();
				sheet.addCell(new Label(col++,xh,addr,formateBody));
//	    			sheet.addCell(new Label(col++,xh,infoBean.getORDER_STATUS_NODE(),formateBody));
				sheet.addCell(new Label(col++,xh,infoBean.getSUM_REAL_MONEY(),formateBody));
//	    			sheet.addCell(new Label(col++,xh,infoBean.getYHXX(),formateBody));
				for(ProductBean product:productList){
					int columnIndex_d = columnIndex;
					
//	    				sheet.mergeCells(columnIndex_d, xh, columnIndex_d, xh+4); 
					sheet.addCell(new Label(columnIndex_d++,xh,product.getPRODUCT_NAME()+"",formateBody));
//	    				sheet.mergeCells(++columnIndex_d, xh, columnIndex_d, xh+4); 
					sheet.addCell(new Label(columnIndex_d++,xh++,product.getPRODUCT_PRICE()+"*"+product.getSELL_AMOUNT(),formateBody));
//	    				sheet.mergeCells(++columnIndex_d, xh, columnIndex_d, xh+4); 
//	    				sheet.addCell(new Label(columnIndex_d,xh,product.getSELL_AMOUNT()+"",formateBody));
//	    				String picUrl = product.getPRODUCT_PIC();
//	    				File imgFile1 = saveToFile(picUrl.substring(picUrl.indexOf("=")+1).replace("jpg", "png"), picUrl);
//	    				
//	    				int rNum = xh;
//	    	    		sheet.addCell(new Label(++columnIndex_d,rNum,"",formateBody));
//	    	    		sheet.mergeCells(columnIndex_d, rNum, columnIndex_d, rNum+4); 
//	    	    		WritableImage image = new WritableImage(columnIndex_d,rNum,1,4,imgFile1);
//	    	    		sheet.addImage(image);
					
//	    				xh = xh+5;
				}
				String yhxx = infoBean.getYHXX();
				String yhStr = "";
				if(yhxx!=null&&!"".equals(yhxx)){
					if(yhxx.indexOf(";")>0){
						String[] yhArr = yhxx.split(";");
						for(int i=0;i<yhArr.length;i++){
							yhStr += (i+1)+"、"+yhArr[i]+";  ";
						}
					}else{
						yhStr += "1、"+yhxx+";  ";
					}
				}
				sheet.mergeCells(columnIndex, xh, columnIndex+1, xh); 
				sheet.addCell(new Label(columnIndex,xh,"优惠信息："+yhStr,formateBody));
				xh++;
			}
		}
		
		//把创建的内容写入到输出流中，并关闭输出流
		workbook.write();
		workbook.close();
		os.close();
	}
	
	public static File saveToFile(String fName,String destUrl) {
		FileOutputStream fos = null;
		BufferedInputStream bis = null;
		byte[] buf = new byte[1024];
		int size = 0;
		File file = new File(fName);
		try {
			System.out.println("============destUrl:"+destUrl);
			if(destUrl.indexOf("https")>=0){
				HttpsURLConnection httpUrl = null;
				URL url = null;
				url = new URL(destUrl);  
				httpUrl = (HttpsURLConnection) url.openConnection();
				
				httpUrl.setHostnameVerifier(new IgnoreHostnameVerifier());
				TrustManager[] tm = { new IgnoreCertificationTrustManger() };
				SSLContext sslContext = SSLContext.getInstance("TLS");
				sslContext.init(null, tm, null);
				SSLSocketFactory ssf = sslContext.getSocketFactory();
				httpUrl.setSSLSocketFactory(ssf);
				
				bis = new BufferedInputStream(httpUrl.getInputStream());
			} else{
				HttpURLConnection httpUrl = null;
				URL url = null;
				url = new URL(destUrl);  
				httpUrl = (HttpURLConnection) url.openConnection();
				httpUrl.setRequestMethod("GET");
				httpUrl.connect();
				bis = new BufferedInputStream(httpUrl.getInputStream());
			}
			System.out.println("============bis:"+bis.toString());
			fos = new FileOutputStream(file);
			while ((size = bis.read(buf)) != -1) {
				fos.write(buf, 0, size);
			}
			fos.flush();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				fos.close();
				bis.close();
			} catch (IOException e) {
			} catch (NullPointerException e) {
			}
		}
		return file;
	}
	
	static class IgnoreCertificationTrustManger implements X509TrustManager {
		private X509Certificate[] certificates;
		public void checkClientTrusted(X509Certificate certificates[],
				String authType) throws CertificateException {
			if (this.certificates == null) {
				this.certificates = certificates;
			}
		}
		public void checkServerTrusted(X509Certificate[] ax509certificate,
				String s) throws CertificateException {
			if (this.certificates == null) {
				this.certificates = ax509certificate;
			}
		}
		public java.security.cert.X509Certificate[] getAcceptedIssuers() {
			return null;
		}
		public void checkClientTrusted(
				java.security.cert.X509Certificate[] chain, String authType)
				throws java.security.cert.CertificateException {

		}
		public void checkServerTrusted(
				java.security.cert.X509Certificate[] chain, String authType)
				throws java.security.cert.CertificateException {
			// TODO Auto-generated method stub
		}
	}

	static class IgnoreHostnameVerifier implements HostnameVerifier {
		public boolean verify(String arg0, SSLSession arg1) {
			return true;
		}
	}
}
